/**
 * Created by Simon on 2017/4/17.
 */

let sqlite3 = require('sqlite3').verbose();
let Fruit = require('../../model/fruit-model');
let db = new sqlite3.Database('./database/king-land.db');

function getAllActiveFruits (callback) {
  let fruits = [];
  db.serialize(function () {
    db.each("SELECT * FROM FRUIT WHERE status = 1 order by id desc", function (err, f) {
      let fruit = new Fruit(f.id, f.cn_name, f.en_name, f.spa_name, f.status, f.cn_brief, f.en_brief, f.spa_name, f.update_time, f.image_url);
      fruits.push(fruit);
    }, function () {
      callback(fruits);
    });
  });
}

function getAllFruits (callback) {
  var fruits = [];
  db.serialize(function () {
    db.each("SELECT * FROM FRUIT order by id desc", function (err, f) {
      fruits.push(new Fruit(f.id, f.cn_name, f.en_name, f.spa_name, f.status, f.cn_brief, f.en_brief, f.spa_name, f.update_time, f.image_url));
    }, function () {
      callback(fruits);
    });
  });
}

function getFruitById (id, callback) {
  var fruit = {};
  db.serialize(function () {
    db.each("SELECT * FROM FRUIT WHERE id = ?", [id], function (err, f) {
      fruit = new Fruit(f.id, f.cn_name, f.en_name, f.spa_name, f.status, f.cn_brief, f.en_brief, f.spa_name, f.update_time, f.image_url);
    }, function () {
      callback(fruit);
    });
  });
  
}

function deleteById (id, callback) {
  db.serialize(function () {
    db.run(`delete from FRUIT where id = ?`, [id], callback);
  });
}

function saveOrUpdate (fruit, callback) {
  db.serialize(function () {
    var insertSql  = `
      insert into FRUIT values (
        $id,
        $cnName,
        $enName,
        $spaName,
        $status,
        $cnBrief,
        $enBrief,
        $spaBrief,
        datetime('now', '+8 hour'),
        $imageUrl
)
    `;
    var updateSql = `
      update FRUIT set
        cn_name = $cnName,
        en_name = $enName,
        spa_name = $spaName,
        status = $status,
        cn_brief = $cnBrief,
        en_brief = $enBrief,
        spa_brief = $spaBrief,
        update_time = datetime('now', '+8 hour'),
        image_url = $imageUrl
        where id =$id
    `;
    var prepareStmt = null;
    if(fruit.id) {
      prepareStmt = db.prepare(updateSql);
    } else {
      prepareStmt = db.prepare(insertSql);
    }
    prepareStmt.run({
      $id: fruit.id || null,
      $cnName: fruit.cnName,
      $enName: fruit.enName,
      $spaName: fruit.spaName,
      $cnBrief: fruit.cnBrief,
      $enBrief: fruit.enBrief,
      $spaBrief: fruit.spaBrief,
      $status: fruit.status,
      $imageUrl: fruit.imageUrl
    });
    prepareStmt.finalize();
    callback();
  });
}

module.exports = {
  getAllActiveFruits: getAllActiveFruits,
  getAllFruits: getAllFruits,
  getFruitById: getFruitById,
  deleteById: deleteById,
  saveOrUpdate: saveOrUpdate
};